import type { DataBaseOptions, TQuery } from 'easy/utils'
import mysql from 'mysql2/promise'
import useDb from 'easy/utils'

class Database {
  constructor(config) {
    this.pool = mysql.createPool(config)
  }

  async query(sql, values) {
    const connection = await this.pool.getConnection()
    try {
      const [rows] = await connection.query(sql, values)
      return rows
    }
    finally {
      connection.release()
    }
  }

  close() {
    return this.pool.end()
  }
}
const db = new Database({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'mydatabase',
})
// 辅助函数，将查询条件对象转换为SQL WHERE语句
function mapSqlQueryConversion(query: TQuery | TQuery[]): { sql: string, values: any[] } {
  const values: any[] = []
  const sql: string[] = []
  function parseQueryObj(where: TQuery) {
    const _sql = Object.keys(where).map((key) => {
      const conversion = where[key] as StrictComparisonType
      if (typeof conversion === 'object') {
        const operation = Object.keys(conversion)[0] as ComparisonOperator
        const value = conversion[operation]
        values.push(value)
        return `${key} ${operation} ?`
      }
      values.push(conversion)
      return `${key} = ?`
    }).join(' AND ')
    sql.push(_sql)
  }
  if (Array.isArray(query)) {
    query.forEach(item => parseQueryObj(item))
  }
  else {
    parseQueryObj(query)
  }
  return { sql: sql.join(' OR '), values }
}
const myDb = useDb({
  create(tableName, data) {
    let keys = []
    let values = []
    if (Array.isArray(data)) {
      keys = Object.keys(data[0])
      values = data.map(item => Object.values(item))
    }
    else {
      keys = Object.keys(data)
      values = Object.values(data)
    }
    const placeholders = keys.map(() => '?').join(',')

    return db.query(`INSERT INTO ${tableName} (${keys.join(',')}) VALUES (${placeholders})`, values)
  },
  findBy(tableName, options = {} as DataBaseOptions) {
    let sql = `SELECT * FROM ${tableName}`
    let values = []

    if (options.where) {
      const res = mapSqlQueryConversion(options.where)
      sql += ` WHERE ${res.sql}`
      values = res.values
    }

    if (options.order) {
      sql += ` ORDER BY ${options.order}`
    }

    if (options.limit) {
      sql += ` LIMIT ${options.limit}`
    }

    return db.query(sql, values)
  },

  update(tableName, data, options = {}) {
    const setClause = Object.keys(data)
      .map(key => `${key} = ?`)
      .join(',')

    const values = Object.values(data)
    let sql = `UPDATE ${tableName} SET ${setClause}`

    if (options.where) {
      const res = mapSqlQueryConversion(options.where)
      sql += ` WHERE ${res.sql}`

      values.push(...res.values)
    }

    return db.query(sql, values)
  },

  destroy(tableName, options = {}) {
    let sql = `DELETE FROM ${tableName}`
    let values = []

    if (options.where) {
      const res = mapSqlQueryConversion(options.where)
      sql += ` WHERE ${res.sql}`
      values = res.values
    }

    return db.query(sql, values)
  },

  async count(tableName, options = {}) {
    let sql = `SELECT COUNT(*) as count FROM ${tableName}`
    let values = []

    if (options.where) {
      const res = mapSqlQueryConversion(options.where)
      sql += ` WHERE ${res.sql}`
      values = res.values
    }

    const res = await db.query(sql, values)
    return res?.[0]?.count
  },
})
// myDb.addOne('users', { name: 'yi', age: 18 })

// myDb.addList('users', [
//   { name: 'yi', age: 18 },
//   { name: 'yi', age: 18 },
// ])

// myDb.removeById('users', 1)

// myDb.removeBy('users', { name: 'yi', age: 30 })

// myDb.updateById('users', 1, { name: 'yi', age: 18 })

// myDb.updateBy('users', { name: 'yi', age: 30, sex: 'dsfsdfdf' }, [{ sex: { '<': 28 }, name: { '>': 'dsfsdfdf' }, age: { '>=': 5 } }, { id: 25 }])

// myDb.findById('users', 1)

// myDb.findBy('users', { where: { name: 'yi', age: 30, sex: 'dsfsdfdf' } })
export default myDb
